import logging
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

yts_warn_router = APIRouter(prefix="/tsWarring", tags=["报警设定管理"], dependencies=[Depends(oauth2_scheme)])


class YtsSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    fscoreFlag: str
    fparafield: str
    feuipmentid: str
    flevelno: str
    fstate: str


@yts_warn_router.get("/list", summary="报警设定查询")
def list_yts_warn(yts: YtsSearch = Query(), db: Session = Depends(get_db)):
    try:
        with db.begin():
            param = {}
            sql = "select count(1) from y_ts_warring where 1=1"
            if yts.fsiteNo is not None:
                sql = sql + " and fsite_no = :fsiteNo "
                param["fsiteNo"] = yts.fsiteNo
            if yts.fscoreFlag is not None:
                sql = sql + " and fscoreFlag = :fscoreFlag "
                param["fscoreFlag"] = yts.fscoreFlag
            if yts.fparafield is not None:
                sql = sql + " and fparafield = :fparafield "
                param["fparafield"] = yts.fparafield
            if yts.feuipmentid is not None:
                sql = sql + " and feuipmentid = :feuipmentid "
                param["feuipmentid"] = yts.feuipmentid
            if yts.flevelno is not None:
                sql = sql + " and flevelno = :flevelno "
                param["flevelno"] = yts.flevelno
            if yts.fstate is not None:
                sql = sql + " and fstate = :fstate "
                param["fstate"] = yts.fstate
            count = db.execute(text(sql), param).scalar()

            param = {}
            sql = "select fwarringno,fsite_no,fscore_flag,fparafield,fbegnum,"
            sql = sql + " fendnum,fdescribe,feuipmentid,flevelno,turn,"
            sql = sql + " interval,fsendmsg,fsendemail,fstate,foperator,fupddate"
            sql = sql + " from y_ts_warring where 1=1"
            if yts.fsiteNo is not None:
                sql = sql + " and fsite_no = :fsiteNo "
                param["fsiteNo"] = yts.fsiteNo
            if yts.fscoreFlag is not None:
                sql = sql + " and fscoreFlag = :fscoreFlag "
                param["fscoreFlag"] = yts.fscoreFlag
            if yts.fparafield is not None:
                sql = sql + " and fparafield = :fparafield "
                param["fparafield"] = yts.fparafield
            if yts.feuipmentid is not None:
                sql = sql + " and feuipmentid = :feuipmentid "
                param["feuipmentid"] = yts.feuipmentid
            if yts.flevelno is not None:
                sql = sql + " and flevelno = :flevelno "
                param["flevelno"] = yts.flevelno
            if yts.fstate is not None:
                sql = sql + " and fstate = :fstate "
                param["fstate"] = yts.fstate
            if yts.order == "desc":
                sql = sql + " order by  feuipmentid desc "
            else:
                sql = sql + " order by  feuipmentid  "
            sql = sql + " limit :limit offset :offset "
            param["offset"] = yts.offset
            param["limit"] = yts.limit
            data = db.execute(text(sql), param).fetchall()

            # details 明细列表
            param = {}
            sql_3 = " select row_number() OVER (PARTITION BY b.fwarringno ORDER BY b.fpoint_no DESC) fentryid"
            sql_3 = sql_3 + ",b.fwarringno,b.fpoint_no,c.fpoint_name"
            sql_3 = sql_3 + " from y_ts_warring a,y_ts_warring_point b,a_ts_point c "
            sql_3 = sql_3 + " where a.fwarringno=b.fwarringno "
            sql_3 = sql_3 + " and b.fpoint_no=c.fpoint_no"
            if yts.fsiteNo is not None:
                sql_3 = sql_3 + " and fsite_no = :fsiteNo "
                param["fsiteNo"] = yts.fsiteNo
            if yts.fscoreFlag is not None:
                sql_3 = sql_3 + " and fscoreFlag = :fscoreFlag "
                param["fscoreFlag"] = yts.fscoreFlag
            if yts.fparafield is not None:
                sql_3 = sql_3 + " and fparafield = :fparafield "
                param["fparafield"] = yts.fparafield
            if yts.feuipmentid is not None:
                sql_3 = sql_3 + " and feuipmentid = :feuipmentid "
                param["feuipmentid"] = yts.feuipmentid
            if yts.flevelno is not None:
                sql_3 = sql_3 + " and flevelno = :flevelno "
                param["flevelno"] = yts.flevelno
            if yts.fstate is not None:
                sql_3 = sql_3 + " and fstate = :fstate "
                param["fstate"] = yts.fstate
            sql_3 = sql_3 + " order by  feuipmentid,b.fentryid "
            data_detail1 = db.execute(text(sql_3), param).fetchall()

            #  人员 明细列表
            param = {}
            sql_2 = " select row_number() OVER (PARTITION BY b.fwarringno ORDER BY b.fuserno DESC) fentryid"
            sql_2 = sql_2 + ",b.fwarringno,b.fuserno,c.username"
            sql_2 = sql_2 + " from y_ts_warring a,y_ts_warring_user b,am_sys_user c "
            sql_2 = sql_2 + " where a.fwarringno=b.fwarringno "
            sql_2 = sql_2 + " and b.fuserno=c.id::text"
            if yts.fsiteNo is not None:
                sql_2 = sql_2 + " and fsite_no = :fsiteNo "
                param["fsiteNo"] = yts.fsiteNo
            if yts.fscoreFlag is not None:
                sql_2 = sql_2 + " and fscoreFlag = :fscoreFlag "
                param["fscoreFlag"] = yts.fscoreFlag
            if yts.fparafield is not None:
                sql_2 = sql_2 + " and fparafield = :fparafield "
                param["fparafield"] = yts.fparafield
            if yts.feuipmentid is not None:
                sql_2 = sql_2 + " and feuipmentid = :feuipmentid "
                param["feuipmentid"] = yts.feuipmentid
            if yts.flevelno is not None:
                sql_2 = sql_2 + " and flevelno = :flevelno "
                param["flevelno"] = yts.flevelno
            if yts.fstate is not None:
                sql_2 = sql_2 + " and fstate = :fstate "
                param["fstate"] = yts.fstate
            sql_2 = sql_2 + " order by  feuipmentid,b.fentryid desc"
            data_detail2 = db.execute(text(sql_2), param).fetchall()

            data_list = []
            for key in range(len(data)):
                d1 = {}
                d1['fwarringno'] = data[key][0]
                d1['fsiteno'] = data[key][1]
                d1['fscoreflag'] = data[key][2]
                d1['fparafield'] = data[key][3]
                d1['fbegnum'] = data[key][4]
                d1['fendnum'] = data[key][5]
                d1['fdescribe'] = data[key][6]
                d1['feuipmentid'] = data[key][7]
                d1['flevelno'] = data[key][8]
                d1['turn'] = data[key][9]
                d1['interval'] = data[key][10]
                d1['fsendmsg'] = data[key][11]
                d1['fsendemail'] = data[key][12]
                d1['fstate'] = data[key][13]
                d1['foperator'] = data[key][14]
                d1['fupddate'] = data[key][15]
                sbillno = data[key][0]

                detail_list1 = []
                for j1 in range(len(data_detail1)):
                    if (str(data_detail1[j1][1]) == str(sbillno)):
                        d2 = {}
                        d2['fwarringno'] = data_detail1[j1][1]
                        d2['fentryid'] = data_detail1[j1][0]
                        d2['fpoint_no'] = data_detail1[j1][2]
                        d2['fpoint_name'] = data_detail1[j1][3]
                        detail_list1.append(d2)

                detail_list2 = []
                for j2 in range(len(data_detail2)):
                    if (str(data_detail2[j2][1]) == str(sbillno)):
                        d3 = {}
                        d3['fwarringno'] = data_detail2[j2][1]
                        d3['fentryid'] = data_detail2[j2][0]
                        d3['fuserno'] = data_detail2[j2][2]
                        d3['username'] = data_detail2[j2][3]
                        detail_list2.append(d3)

                d1["details"] = detail_list1
                d1["atsList2"] = detail_list2
                data_list.append(d1)

            info_dict = {"list": data_list,
                         "limit": yts.limit,
                         "page": yts.pageIndex,
                         "pages": (count + yts.limit - 1) // yts.limit,
                         "total": count}
            return BaseResponse.success(data=info_dict)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class WarringPoint(BaseModel):
    """报警关联的教室点位实体类"""
    fpointNo: str = Field(..., description="点位编号")


class WarringUser(BaseModel):
    """报警关联的人员实体类"""
    fuserno: int = Field(..., description="人员编号", ge=1)


class NewAlarmCreate(BaseModel):
    """新版报警主表创建实体类"""
    fsiteNo: str = Field(..., description="站点编号")
    foperator: str = Field(..., description="操作人")
    fupddate: str = Field(..., description="更新日期")
    fdescribe: Optional[str] = Field(None, description="报警描述")
    fbegnum: str = Field("0", description="开始数值")
    fendnum: str = Field("0", description="结束数值")
    fequipmenttag: str = Field("0", description="设备标签")
    flevelno: str = Field("0", description="报警级别")
    fparafield: Optional[str] = Field(None, description="参数字段")
    fscoreFlag: str = Field("0", description="评分标志")
    fsendemail: str = Field("0", description="是否发送邮件")
    fsendmsg: str = Field("0", description="是否发送消息")
    fstate: str = Field("0", description="报警状态")
    interval: str = Field("0", description="间隔时间")
    turn: str = Field("0", description="轮询标志")

    details: List[WarringPoint] = Field([], description="教室点位列表")
    details2: List[WarringUser] = Field([], description="人员列表")
    fwarringno: int = Field(None, description="编号，修改时填入")


@yts_warn_router.post("/create", summary="新增报警设定")
def create_yts_warn(alarm: NewAlarmCreate, db: Session = Depends(get_db)):
    try:
        d1 = {}
        with db.begin():
            # 获取编号
            sql = "select nextval('a_ts_warring_fwarringno_seq') as last_value "
            res = db.execute(text(sql)).fetchall()
            fwarringno = res[0][0]

            sql = "insert into y_ts_warring(fwarringno,fsite_no,fscore_flag,fparafield,fbegnum,fendnum,fdescribe,feuipmentid,flevelno,turn,interval,fsendmsg,fsendemail,fstate,foperator,fupddate)"
            sql = sql + (" values( :fwarringno , :fsiteNo , :fscoreFlag , :fparafield , :fbegnum , :fendnum , "
                         ":fdescribe , :fequipmenttag , :flevelno , :turn , :interval , :fsendmsg , :fsendemail , :fstate , :foperator ,now() ) ")
            db.execute(text(sql), {
                "fwarringno": fwarringno,
                "fsiteNo": alarm.fsiteNo,
                "fscoreFlag": alarm.fscoreFlag,
                "fparafield": alarm.fparafield,
                "fbegnum": alarm.fbegnum,
                "fendnum": alarm.fendnum,
                "fdescribe": alarm.fdescribe,
                "fequipmenttag": alarm.fequipmenttag,
                "flevelno": alarm.flevelno,
                "turn": alarm.turn,
                "interval": alarm.interval,
                "fsendmsg": alarm.fsendmsg,
                "fsendemail": alarm.fsendemail,
                "fstate": alarm.fstate,
                "foperator": alarm.foperator,
            })
            # 教室列表
            atsList2 = []
            atsList2 = alarm.details
            for ats in atsList2:
                sql = "insert into y_ts_warring_point(fwarringno,fpoint_no)"
                sql = sql + " values( :fwarringno , :fpointNo );"
                db.execute(text(sql), {
                    "fwarringno": fwarringno,
                    "fpointNo": ats.fpointNo,
                })

            # 人员列表
            atsList1 = []

            atsList1 = alarm.details2
            for ats in atsList1:
                sql = "insert into y_ts_warring_user(fwarringno,fuserno)"
                sql = sql + " values( :fwarringno , :fuserno );"
                db.execute(text(sql), {
                    "fwarringno": fwarringno,
                    "fuserno": ats.fuserno,
                })

            d1['fwarringno'] = fwarringno
            d1['fsiteNo'] = alarm.fsiteNo
            d1['fscoreFlag'] = alarm.fscoreFlag
            d1['fparafield'] = alarm.fparafield
            d1['fbegnum'] = alarm.fbegnum
            d1['fendnum'] = alarm.fendnum
            d1['fdescribe'] = alarm.fdescribe
            d1['feuipmentid'] = alarm.fequipmenttag
            d1['flevelno'] = alarm.flevelno
            d1['turn'] = alarm.turn
            d1['interval'] = alarm.interval
            d1['fsendmsg'] = alarm.fsendmsg
            d1['fsendemail'] = alarm.fsendemail
            d1['fstate'] = alarm.fstate
            d1['foperator'] = alarm.foperator
            d1['fupddate'] = alarm.fupddate
            d1["details"] = atsList1
            d1["details2"] = atsList2
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@yts_warn_router.post("/update", summary="更新报警设定")
def update_yts_warn(alarm: NewAlarmCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "update y_ts_warring"
            sql = sql + " set fscore_flag= :fscoreFlag ,fparafield= :fparafield "
            sql = sql + ",fbegnum= :fbegnum ,fendnum= :fendnum ,fdescribe= :fdescribe "
            sql = sql + ",flevelno= :flevelno ,turn= :turn ,interval= :interval "
            sql = sql + ",fsendmsg= :fsendmsg ,fsendemail= :fsendemail ,fstate= :fstate "
            sql = sql + ",foperator= :foperator ,fupddate=now()"
            sql = sql + " where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fscoreFlag": alarm.fscoreFlag,
                "fparafield": alarm.fparafield,
                "fbegnum": alarm.fbegnum,
                "fendnum": alarm.fendnum,
                "fdescribe": alarm.fdescribe,
                "flevelno": alarm.flevelno,
                "turn": alarm.turn,
                "interval": alarm.interval,
                "fsendmsg": alarm.fsendmsg,
                "fsendemail": alarm.fsendemail,
                "fstate": alarm.fstate,
                "foperator": alarm.foperator,
                "fwarringno": alarm.fwarringno
            })
            # 教室列表
            sql = "delete from y_ts_warring_point where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": alarm.fwarringno
            })
            atsList2 = alarm.details
            for ats in atsList2:
                sql = "insert into y_ts_warring_point(fwarringno,fpoint_no)"
                sql = sql + " values( :fwarringno , :fpointNo );"
                db.execute(text(sql), {
                    "fwarringno": alarm.fwarringno,
                    "fpointNo": ats.fpointNo,
                })
            # 人员列表
            sql = "delete from y_ts_warring_point where y_ts_warring_user= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": alarm.fwarringno
            })
            # 人员列表
            atsList1 = alarm.details2
            for ats in atsList1:
                sql = "insert into y_ts_warring_user(fwarringno,fuserno)"
                sql = sql + " values( :fwarringno , :fuserno );"
                db.execute(text(sql), {
                    "fwarringno": alarm.fwarringno,
                    "fuserno": ats.fuserno,
                })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@yts_warn_router.post("/delete/{fwarringno}", summary="删除报警设定")
def delete_yts_warn(fwarringno: str = Path(), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from y_ts_warring where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": fwarringno,
            })
            sql = "delete from y_ts_warring_point where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": fwarringno,
            })
            sql = "delete from y_ts_warring_user where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": fwarringno,
            })
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
